package com.erp.framework.util.excelupload;

import java.io.InputStream;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class ExcelUploadBuilder {
	public static final int HEADER_LABEL_ROW_NUM_STRART = 4;
	public static final int HEADER_LABEL_ROW_NUM_END = 7;
	public static final String TABLE_NAME = "T_UPLOAD_EXCEL";   //store data table
    private String jobId;
    
	public String getCurrentJobId() {
		return jobId;
	}
	
	public void BuildJobId(){
		this.jobId = ExcelIOUtil.getCurrentTimeId();
	}
	
	public HashMap BuildExcelHeaderMapping(HSSFSheet sheet) throws Exception {
		HashMap excelHeaderMap = new HashMap();
		try {
			short maxLastCellNum = 0;
			ArrayList headerRows = new ArrayList();
			if(HEADER_LABEL_ROW_NUM_END >= HEADER_LABEL_ROW_NUM_STRART){
				short lastCellNum = 0;
				for(int i=HEADER_LABEL_ROW_NUM_STRART; i<=HEADER_LABEL_ROW_NUM_END; i++){
					HSSFRow excelRow = sheet.getRow(i-1);
					headerRows.add(excelRow);
					if(excelRow!=null) lastCellNum = excelRow.getLastCellNum();
					if(lastCellNum>maxLastCellNum) maxLastCellNum = lastCellNum;
				}
				//Columns loop
				for (short j = 0; j <= maxLastCellNum; j++) {
					//Rows loop
					StringBuffer strBuf = new StringBuffer("");
					for(short k=0; k<headerRows.size(); k++){
						HSSFRow excelRow = (HSSFRow)headerRows.get(k);
						HSSFCell cell = (HSSFCell) excelRow.getCell((short) j);
						strBuf.append(CommonUtil.nullToString(ExcelIOUtil.readValueFromCell(cell, "String")).trim());
					}
					String labelKey = strBuf.toString()
					                        .toUpperCase()
					                        .trim();
					if (!CommonUtil.isEmpty(labelKey)&&!excelHeaderMap.containsKey(labelKey)) {
						excelHeaderMap.put(labelKey, String.valueOf(j));
						Logger.log("Mapping: "+labelKey+ "  Column Num:"+ String.valueOf(j));
					}
				}
			}else{
				throw new Exception("HEADER_LABEL_ROW_NUM_STRART can't greater than HEADER_LABEL_ROW_NUM_END.");
			}
		} catch (Exception e) {
			e.printStackTrace();
			Logger.logExceptions(e);
		}
		return excelHeaderMap;
	}

	public ArrayList BuildExcelDataList(String companyCode, HSSFSheet sheet, ExcelRowFactory factory, HashMap headerMap) throws Exception{
		ArrayList dataList = new ArrayList();
		try {
			boolean existsUploadField = true;
            String jobId = getCurrentJobId();
			int lastRowNum = sheet.getLastRowNum();
			ArrayList protoCellList = factory.getInstance().getProtoCellList();
			if(protoCellList==null||protoCellList.size()<1){
				existsUploadField = false;
			}
			if(!existsUploadField){
				Logger.log("there are no record at mapping table: T_EXCEL_MAPPING");
			}
			
			for (int i = HEADER_LABEL_ROW_NUM_END; existsUploadField &&i <= lastRowNum; i++) {
				HSSFRow row = sheet.getRow(i);
				if(row==null) continue;
				StringBuffer rowApp = new StringBuffer("");
				ExcelRowVo rowVo = factory.createExcelRowVo();
				ArrayList cellList = rowVo.getRowCellList();
				
				for (int j = 0; cellList != null && j < cellList.size(); j++) {
					ExcelCellVo cellVo = (ExcelCellVo) cellList.get(j);
					if(cellVo==null) continue;
					String labelKey = CommonUtil.trim(cellVo.getExcelLabel()).toUpperCase();
					String index = CommonUtil.nullToString(headerMap.get(labelKey));
					if(CommonUtil.isValidPositiveIntegerOrZero(index)){
						int columnNum = Integer.parseInt(index);
						HSSFCell cell = (HSSFCell) row.getCell((short) columnNum);
						String cellValue = CommonUtil.nullToString(ExcelIOUtil.readValueFromCell(cell, cellVo.getCellType()));
						cellVo.setCellValue(cellValue);
						rowApp.append(cellValue);
					} else {
						throw new Exception("No column label in excel file related to DB column :" + labelKey);
					}
				}
				if(!CommonUtil.isEmpty(rowApp.toString())){
					rowVo.setCompanyCode(companyCode);
					rowVo.setExcelLineNo(new CustomBigInteger(row.getRowNum()));
					rowVo.setJobId(jobId);
					dataList.add(rowVo);
				}
			}
		} catch (Exception e) {
			throw e;
		}
		return dataList;
	}
	
	public void validateDataList(){
		
	}
	
	public void UploadExcel(String companyCode, InputStream inStream, Connection conn) throws Exception{
		String jobId = "";
		try {
			POIFSFileSystem fs = new POIFSFileSystem(inStream);
			HSSFWorkbook hssfBook = new HSSFWorkbook(fs);
			HSSFSheet sheet = hssfBook.getSheetAt(0);
			//
			BuildJobId();
			/* Initialize the mapping between DB Column and excel Column Label
			 * from DB and construct prototype ExcelRow Vo
			 */
			ExcelRowFactory factory = ExcelRowFactory.getInstance();
			factory.initProtoExcelRow(conn);
			
			//Initialize the mapping between Column Num and Column Label 
			HashMap headerMap = BuildExcelHeaderMapping(sheet);
			
			//read Data form excel file stream
			ArrayList dataList = BuildExcelDataList(companyCode, sheet, factory, headerMap);
			
			validateDataList();
			//insert data to DB
			ExcelUploadDao dao = new ExcelUploadDao();
			dao.insertDataToDb(dataList, conn);
		} catch (Exception e) {
			Logger.logExceptions(this, e);
			throw e;
		}
	}

}
